﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace syc.Sale
{
    public partial class 公司合同审批表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected string 客户名称;
        protected string 客户全称;
        protected string 地址;
        protected string 联系人;
        protected string 电话;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                Leibie_SelectedIndexChanged(null, null);
               
            }
        }
       
        protected void WriteToFile()
        {          
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + DateTime.Now.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/Sale/模板/公司合同审批表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "公司合同审批表";
            hssfworkbook.SummaryInformation = si;
        }
        protected string GetCustomer(string 客户名称)
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            string sql = @"
            select 详细名称 from 原料供货单位 where 名称='@名称'";
            sql = sql.Replace("@名称", 客户名称);
            string sql1 = @"
            select 详细名称 from 辅料供货单位 where 名称='@名称'";
            if (this.Leibie.Text == "1")
            {
                sql = sql.Replace("@名称", 客户名称);
                Cmd.CommandText = sql;
            }
            else
            {
                sql1 = sql1.Replace("@名称", 客户名称);
                Cmd.CommandText = sql1;
            }
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                if (Reader.IsDBNull(0))
                {
                    客户全称 = "";
                }
                else
                {
                    客户全称 = Reader.GetString(0);
                }
            }
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();

            return 客户全称;
        }
        protected void Button_Click(object sender, EventArgs e)
        {

            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("公司合同审批表");            
           
            sheet1.GetRow(1).GetCell(1).SetCellValue(this.TextBox1.Text);
            sheet1.GetRow(2).GetCell(1).SetCellValue(this.TextBox2.Text);
            if (this.Leibie.Text == "2")
            {
                客户名称 = this.Customer1.SelectedItem.Text;
                sheet1.GetRow(3).GetCell(4).SetCellValue("阮 阳");
                sheet1.GetRow(3).GetCell(6).SetCellValue(84622316);
            }
            else
            {
                客户名称 = this.Customer.SelectedItem.Text;
            }
            客户全称= GetCustomer(客户名称);
            sheet1.GetRow(4).GetCell(2).SetCellValue(客户全称);
            sheet1.GetRow(5).GetCell(2).SetCellValue(this.Address.Text);
            sheet1.GetRow(6).GetCell(2).SetCellValue(this.LXR.Text);
            sheet1.GetRow(6).GetCell(5).SetCellValue(this.Tele.Text);

            //string op = (string)this.Session["OperatorName"];
            //if (op != null)
            //{
            //    sheet1.GetRow(9).GetCell(5).SetCellValue(op);
            //}
            //sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

        protected void Customer_SelectedIndexChanged(object sender, EventArgs e)
        {        
            string id="1";
            if (this.Leibie.Text == "1")
            {
                id = this.Customer.Text;
                地址 = this.DZList.Items.FindByValue(id).Text;
                联系人 = this.LXRList.Items.FindByValue(id).Text;
                电话 = this.TeleList.Items.FindByValue(id).Text;
            }
            if (this.Leibie.Text == "2")
            {
                id = this.Customer1.Text;
                地址 = this.DZList1.Items.FindByValue(id).Text;
                联系人 = this.LXRList1.Items.FindByValue(id).Text;
                电话 = this.TeleList1.Items.FindByValue(id).Text;
            }
            this.Address.Text = 地址;
            this.LXR.Text = 联系人;
            this.Tele.Text = 电话;
        }

        protected void Leibie_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.Leibie.Text == "1")
            {
                Customer.DataBind();
                this.DZList.DataBind();
                this.LXRList.DataBind();
                this.TeleList.DataBind();
            }
            if (this.Leibie.Text == "2")
            {
                Customer1.DataBind();
                this.DZList1.DataBind();
                this.LXRList1.DataBind();
                this.TeleList1.DataBind();
            }           
            Customer_SelectedIndexChanged(null, null);
        }

       
    }
}
